create database StudentManagement1
go
use StudentManagement1
go
raiserror('Create table Course',0,1)
create table Course
(
	cCourseID char(5) not null primary key,
	vCourseName varchar(30) not null	
)
go
raiserror('Create table Role',0,1)
create table Role
(
	cRoleID char(3) primary key,
	vRoleDetail varchar(30)
)
go
raiserror('Create table Login',0,1)
create table Login
(
	cLoginID varchar(20) not null primary key,
	vPassword varchar(15) default '12345678@X' ,
	cRoleID char(3) not null,
	iState int default 0,
	constraint fk_Login_Role foreign key (cRoleID) references Role(cRoleID)
)
go
raiserror('Create table Person',0,1)
create table Person
(
	cPersonID char(9) not null primary key,
	vPersonName varchar(30) not null	,
	dPersonBirth datetime not null,
	vPersonAdd varchar(50) not null,
	vPersonPhone varchar(11) not null,
	vPersonMail varchar(20) ,
	vPicture varchar(40) ,
	cLoginID varchar(20) not null,
	constraint fk_Person_Login foreign key (cLoginID) references Login(cLoginID)
)
go
raiserror('Create table Class',0,1)
create table Class
(
	cClassID char(6) not null primary key,
	vClassName varchar(20) not null	,
	cCourseID char(5) not null ,
	constraint fk_Class_Course foreign key (cCourseID) references Course(cCourseID),
)
go
raiserror('Create table Subject',0,1)
create table Subject
(
	cSubjectID char(5) not null primary key,
	vSubjectName varchar(40) not null,
	cCourseID char(5) not null,
	constraint fk_Subject_Course foreign key (cCourseID) references Course(cCourseID),
)
go
raiserror('Create table Subject Detail',0,1)
create table SubjectDetail
(
	cSubjectID char(5) not null,
	cStaffID char(9) not null,
	cClassID char(6) not null , 
	constraint fk_SubjectDetail_Class foreign key (cClassID) references Class(cClassID),
	constraint fk_SubjectDetail_Person foreign key (cStaffID) references Person(cPersonID),
	constraint fk_SubjectDetail_Subject foreign key (cSubjectID) references Subject(cSubjectID),
	primary key(cClassID,cSubjectID)
)
go
raiserror('Create table Student',0,1)
create table Student
(
	cStudentID char(9) not null primary key,
	cClassID char(6) not null ,
	iStatusOfStudying int not null,
	constraint fk_Student_Person foreign key (cStudentiD) references Person(cPersonID)
)
go
raiserror('Create table Marks',0,1)
create table Marks
(
	cStudentID char(9) not null primary key,
	iMark int constraint chkMarkOfStudent check (iMark <= 10) not null,
	constraint fk_Marks_Student foreign key (cStudentID) references Student(cStudentID)
)
go
raiserror('Create table Rollcall',0,1)
create table Rollcall
(
	cStudentID char(9) not null ,
	dRollcallDay datetime not null ,
	iAttend int default 0 ,
	constraint pk_Rollcall primary key (cStudentID,dRollcallDay),
	constraint fk_Rollcall_Student foreign key (cStudentID) references Student(cStudentID)
)
go
raiserror('Create table FeeType',0,1)
create table FeeType
(
	cTypeID char(3) not null primary key,
	vTypeName varchar(30) not null,
	iFee	int not null
)
go
raiserror('Create table Fee',0,1)
create table Fee
(
	cStudentID char(9) not null primary key,
	cTypeID char(3) not null,
	iFeePaid int default 0,
	constraint fk_Fee_Student foreign key (cStudentID) references Student(cStudentID),
	constraint fk_Fee_FeeType foreign key (cTypeID) references FeeType(cTypeID)
)
go
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
----------------------------------------COURSE---------------------------------------------
--Procedure insert Course
create procedure prcInsertCourse @vCourseName varchar(30)
as
begin
	declare @maxId_num char(5)
	if(exists(select * from Course))
	begin	
		select top 1 @maxId_num=cCourseID 
		from Course
		order by right(cCourseID,4) desc
		set @maxId_num='C'+convert(char(4),convert(int,right(@maxId_num,4)+1))
	end
	else
	begin
		set @maxId_num='C1001'
	end
	insert into Course
	values(@maxId_num,@vCourseName)
end
go
--procedure update Course
create procedure prcUpdateCourse @cCourseID char(5),@vCourseName varchar(30)
as
begin
	update Course set vCourseName = @vCourseName where cCourseID = @cCourseID
end
go
--procedure Delete Course
create procedure prcDeleteCourse @cCourseID char(5)
as
begin
	Delete from Course where cCourseID = @cCourseID
end
go
--procedure Get All Courses
create procedure prcGetAllCourses 
as
begin
	select * from Course
end
go
--procedure get course by CourseID
create procedure prcGetCourseByCourseID @cCourseID char(5)
as
begin
	select * from Course where cCourseID = @cCourseID
end
go
----------------------------------------ROLE-----------------------------------------------
--Procedure insert Role
create procedure prcInsertRole @vRoleDetail varchar(30)
as
begin
	declare @maxId_num char(3)
	if(exists(select * from Role))
	begin	
		select top 1 @maxId_num=cRoleID 
		from Role
		order by right(cRoleID,2) desc
		set @maxId_num='R'+convert(char(2),convert(int,right(@maxId_num,2)+1))
	end
	else
	begin
		set @maxId_num='R11'
	end
	insert into Role
	values(@maxId_num,@vRoleDetail)
end
go
--Procedure Update Role
create procedure prcUpdateRole @cRoleID char(3),@vRoleDetail varchar(30)
as
begin
	update Role
	set vRoleDetail =@vRoleDetail
	where cRoleID = @cRoleID
end
go
--Procedure Get Role by RoleID
create procedure prcGetRoleByRoleID @cRoleID char(3)
as
begin
	select * from Role where cRoleID = @cRoleID
end
go
--Procedure get all roles 
create procedure prcGetAllRoles 
as
begin
	select * from Role
end
go
--Procedure get role by loginID
create procedure prcGetRoleByLoginID @cLoginID varchar(20)
as
begin
	select * from Role a,Login b
	where a.cRoleId = b.cRoleID
	and b.cLoginID =@cLoginID
end
go
----------------------------------------LOGIN----------------------------------------------
--Procedure insert Login
create procedure prcInsertAccount @cLoginID varchar(20),@cRoleID char(3),@iState tinyint
as
begin
	insert Login values(@cLoginID,default,@cRoleID,@iState)
end
go
--Procedure update Login
create procedure prcUpdateAccount @cLoginID varchar(20),@cRoleID char(3),@iState tinyint
as
begin
	Update Login 
	set cRoleID = @cRoleID,iState = @iState
	where cLoginID = @cLoginID
end
go
--Procedure update password of login
create procedure prcUpdatePasswordOfAccount @cLoginID varchar(20),@vPassword varchar(15)
as
begin
	Update Login 
	set vPassword = @vPassword
	where cLoginID = @cLoginID
end
go
--Procedure delete account
create procedure prcDeleteAccount @cLoginID varchar(20)
as
begin
	delete from Login where cLoginID = @cLoginID
end
go
--Procedure get all account
create procedure prcGetAllAccounts 
as
begin
	select * from Login	
end
go
--Procedure get account by LoginID
create procedure prcGetAccountByLoginID @cLoginID varchar(20)
as
begin
	select * from Login	where cLoginID = @cLoginID
end
go
--Procedure get account by LoginID and Password
create procedure prcGetAccountByLoginIDAndPassword @cLoginID varchar(20),@vPassword varchar(15)
as
begin
	select * from Login	where cLoginID = @cLoginID and vPassword = @vPassword
end
go
--Procedure Test for existing of this loginID 

----------------------------------------PERSON---------------------------------------------
--Procedure insert Person
create procedure prcInsertPerson @vPersonName varchar(30) ,@dPersonBirth datetime ,@vPersonAdd varchar(50) ,@vPersonPhone varchar(11) ,@vPersonMail varchar(20) ,@vPicture varchar(40) ,@cLoginID varchar(20) 
as
begin
	declare @maxId_num char(9)
	if(exists(select * from Person))
	begin	
		select top 1 @maxId_num=cPersonID 
		from Person
		order by right(cPersonID,8) desc
		set @maxId_num='P'+convert(char(8),convert(int,right(@maxId_num,8)+1))
	end
	else
	begin
		set @maxId_num='P10000001'
	end
	set @vPicture = 'Image\' + @vPicture;
	insert into Person
	values(@maxId_num,@vPersonName,@dPersonBirth,@vPersonAdd,@vPersonPhone,@vPersonMail,@vPicture,@cLoginID)
end
go
--Procedure update Person
create procedure prcUpdatePerson @cPersonID char(9),@vPersonName varchar(30) ,@dPersonBirth datetime ,@vPersonAdd varchar(50) ,@vPersonPhone varchar(11) ,@vPersonMail varchar(20) ,@vPicture varchar(40) 
as
begin
	set @vPicture = 'Image\' + @vPicture;
	update Person
	set vPersonName=@vPersonName,dPersonBirth=@dPersonBirth,vPersonAdd=@vPersonAdd,vPersonPhone=@vPersonPhone,vPersonMail=@vPersonMail,vPicture=@vPicture
	where cPersonID = @cPersonID
end
go
--Procedure delete person
create procedure prcDeletePerson @cPersonID char(9)
as
begin
	delete from Person where cPersonID = @cPersonID
end
go
--Procedure get all persons
create procedure prcGetAllPersons 
as
begin
	select * from Person
end
go
--Procedure get person by PersonID
create procedure prcGetPersonByPersonID @cPersonID char(9)
as
begin
	select * from Person where cPersonID = @cPersonID
end
go
--Procedure get person by PersonID
create procedure prcGetPersonByLoginID @cLoginID varchar(20)
as
begin
	select * from Person where cLoginID = @cLoginID
end
go
--Procedure get Person with role
create procedure prcGetPersonWithRoleID @cRoleID char(3)
as
begin
	select a.* 
	from Person a,Login b,Role c 
	where a.cLoginID = b.cLoginID 
	and b.cRoleId = c.cRoleID
	and c.cRoleID = @cRoleID
end
go
--Procedure get person except this role
create procedure prcGetPersonExceptRoleID @cRoleID char(3)
as
begin
	select a.* 
	from Person a,Login b,Role c 
	where a.cLoginID = b.cLoginID 
	and b.cRoleId = c.cRoleID
	and c.cRoleID <> @cRoleID
end
go
--Procedure get Person with Class
create procedure prcGetPersonWithClassID @cClassID char(6)
as
begin
	select a.*
	from Person a,Student b
	where a.cPersonID = b.cStudentID
	and b.cClassID = @cClassID
end
go
--Procedure get Person with RoleID and ClassID
create procedure prcGetTrainerByClassID @cClassID char(6)
as
begin
	select a.*
	from Person a,Subject b,SubjectDetail c
	where a.cPersonID = c.cStaffID
	and b.cSubjectID = c.cSubjectID
	and c.cClassID = @cClassID
end
go
--Procedure get new PersonID
create procedure prcGetNewPersonID
as
begin
	select top 1 *
	from Person
	order by right(cPersonID,8)desc
end
go
--Procedure get staff by classID
--create procedure prcGetStaffByClassID @cClassID char(6)
--as
--begin
--	select a.cPersonID,a.vPersonName
--	from Person a,Class c
--	where a.cPersonID = c.cStaffID
--	and c.cClassID = @cClassID
--end
--go
----------------------------------------CLASS----------------------------------------------
--Procedure insert Class
create procedure prcInsertClass @vClassName varchar(20) ,@cCourseID char(5)  
as
begin
	declare @maxId_num char(6)
	if(exists(select * from Class))
	begin	
		select top 1 @maxId_num=cClassID 
		from Class
		order by right(cClassID,4) desc
		set @maxId_num='CL'+convert(char(4),convert(int,right(@maxId_num,4)+1))
	end
	else
	begin
		set @maxId_num='CL1001'
	end
	insert into Class
	values(@maxId_num,@vClassName,@cCourseID)
end
go
--Procredure Update Class
create procedure prcUpdateClass @cClassID char(6),@vClassName varchar(30),@cCourseID char(5)
as
begin
	update Class
	set vClassName = @vClassName,cCourseID = @cCourseID
	where cClassID = @cClassID
end
go
--procedure delete Class
create procedure prcDeleteClass @cClassID char(6)
as
begin
	Delete
	from Class
	where cClassID = @cClassID
end
go
--Procedure Get Class by ClassID
create procedure prcGetClassByClassID @cClassID char(6)
as
begin
	select * from Class where cClassID = @cClassID
end
go
--Procedure Get Class by CourseID
create procedure prcGetClassByCourseID @cCourseID char(5)
as
begin
	select * from Class where cCourseID = @cCourseID
end
go
--Procedure Get Class by StaffID
--create procedure prcGetClassByStaffID @cStaffID char(9)
--as
--begin
--	select * from Class a,ClassDetail b where a.cClassID = b.cClassID and b.cStaffID = @cStaffID
--end
go
--Procedure get all Classes 
create procedure prcGetAllClasses 
as
begin
	select * from Class
end
go
--prcocedure get all students of class
create procedure prcGetAllStudentsOfClass @cClassID char(6),@cSubjectID char(9)
as
begin
	select c.cPersonID,c.vPersonName,c.dPersonBirth,a.vClassName,d.vPersonName 'vTrainerName',e.vCourseName,f.vSubjectName
	from Class a,Student b,Person c, Person d,Course e,Subject f,SubjectDetail g
	where a.cClassID = b.cClassID
	and b.cStudentID = c.cPersonID
	and a.cCourseID = e.cCourseID
	and f.cSubjectID = g.cSubjectID
	and a.cClassID = g.cClassID
	and g.cStaffID = d.cPersonID
	and a.cClassID = @cClassID
	and f.cSubjectID = @cSubjectID
end
go
----------------------------------------SUBJECT-----------------------------------------------
--Procedure insert subject
create procedure prcInsertSubject @vSubjectName varchar(40),@cCourseID char(5)
as
begin
	declare @maxId_num char(5)
	if(exists(select * from Subject))
	begin	
		select top 1 @maxId_num=cSubjectID 
		from Subject
		order by right(cSubjectID,4) desc
		set @maxId_num='S'+convert(char(4),convert(int,right(@maxId_num,4)+1))
	end
	else
	begin
		set @maxId_num='S1001'
	end
	insert into Subject
	values(@maxId_num,@vSubjectName,@cCourseID)
end
go
--Procedure Update Subject
create procedure prcUpdateSubject @cSubjectID char(5),@vSubjectName varchar(40),@cCourseID char(5)
as
begin
	update Subject
	set vSubjectName =@vSubjectname,cCourseID = @cCourseID
	where cSubjectID = @cSubjectID
end
go
--Procedure Delete Subject
create procedure prcDeleteSubject @cSubjectID char(5)
as
begin
	delete 
	from Subject
	where cSubjectID = @cSubjectID
end
go
--Procedure Get Subject by SubjectID
create procedure prcGetSubjectBySubjectID @cSubjectID char(5)
as
begin
	select * from Subject where cSubjectID = @cSubjectID
end
go
--Procedure get all subjects
create procedure prcGetAllSubjects 
as
begin
	select * from Subject
end
go
--Procedure get subect by ClassID 
create procedure prcGetSubjectByClassID @cClassID char(6)
as
begin
	--select a.cSubjectID,a.cSubjectName,b.cClassID,b.cStaffID from Subject a,SubjectDetail b
	select a.cSubjectID,a.vSubjectName ,a.cCourseID
	from Subject a,Class b,Course c
	where a.cCourseID = c.cCourseID
	and b.cCourseID = c.cCourseID
	and b.cClassID = @cClassID

end
go
--Procedure get subject by StaffID
create procedure prcGetSubjectByStaffID @cStaffID char(9)
as
begin
	select b.cSubjectID,a.vSubjectName from Subject a,SubjectDetail b
	where a.cSubjectID = b.cSubjectID
	and b.cStaffID = @cStaffID
end
go
----------------------------------------SUBJECT DETAIL--------------------------------------------
--procedure insert SubjectDetail
create procedure prcInsertSubjectDetail @cSubjectID char(5),@cStaffID char(9),@cClassID char(6)
as
begin
	insert into SubjectDetail
	values(@cSubjectID,@cStaffID,@cClassID)
end
go
--procedure update SubjectDetail
create procedure prcUpdateSubjectDetail @cSubjectID char(5),@cStaffID char(9),@cClassID char(6)
as
begin
	update SubjectDetail
	set cStaffID = @cStaffID
	where cClassID = @cClassID and cSubjectID = @cSubjectID
end
go
--procedure delete SubjectDetail
create procedure prcDeleteSubjectDetail @cSubjectID char(5),@cStaffID char(9),@cClassID char(6)
as
begin
	Delete
	from SubjectDetail
	where cClassID = @cClassID and cSubjectID = @cSubjectID
end
go
--procedure get All SubjectDetails 
create procedure prcGetAllSubjectDetails 
as
begin
	select * from SubjectDetail
end
go
--procedure get SubjectDetail by ClassID
create procedure prcGetSubjectDetailsByClassID @cClassID char(6)
as
begin
	select * from SubjectDetail where cClassID = @cClassID
end
go
--procedure get SubjectDetail by StaffID
create procedure prcGetSubjectDetailsByStaffID @cStaffID char(9)
as
begin
	select * from SubjectDetail where cStaffID = @cStaffID
end
go
--procedure get SubjectDetail by SubjectID
create procedure prcGetSubjectDetailsBySubjectID @cSubjectID char(5)
as
begin
	select * from SubjectDetail where cSubjectID = @cSubjectID
end
go
--procedure get Subject by ClassID and StaffID
create procedure prcGetSubjectDetailByClassIDAndSubjectID @cClassID char(6),@cSubjectID char(5)
as
begin
	select * from SubjectDetail where cClassID = @cClassID and cSubjectID = @cSubjectID
end
go
----------------------------------------STUDENT--------------------------------------------
--Procedure insert Student 
create procedure prcInsertStudent @cStudentID char(9),@cClassID char(6)
as
begin
	insert Student values(@cStudentID,@cClassID,1)
end
go
--Procedure update Student
create procedure prcUpdateStudent @cStudentID char(9),@cClassID char(6)
as
begin
	update Student
	set cClassID = @cClassID
	where cStudentID = @cStudentID
end
go
--Procedure delete student
create procedure prcDeleteStudent @cStudentID char(9)
as
begin
	delete from Student where cStudentID = @cStudentID
end
go
--Procedure update status of Student
create procedure prcUpdateStatusOfStudent @cStudentID char(9),@iStatusOfStudying int
as
begin
	update Student
	set iStatusOfStudying = @iStatusOfStudying
	where cStudentID = @cStudentID
end
go
--Procedure get all students
create procedure prcGetAllStudents
as
begin
	select * from Student
end
go
--Procedure get all students by StudentID
create procedure prcGetStudentbyStudentID @cStudentID char(9)
as
begin
	select * from Student where cStudentID = @cStudentID
end
go
--go
--Procedure get all student by ClassID
create procedure prcGetStudentsByClassID @cClassID char(6)
as
begin
	select * from Student
	where cClassID = @cClassID
end
go
----------------------------------------MARKS----------------------------------------------
--Procedure insert Marks
create procedure prcInsertMarks @cStudentID char(9),@iMark int
as
begin
	insert Marks values(@cStudentID,@iMark)
end
go
--procedure update Marks
create procedure prcUpdateMarks @cStudentID char(9),@iMark int
as
begin
	update Marks set iMark = @iMark where cStudentID = @cStudentID
end
go
--procedure delete Marks
create procedure prcDeleteMarks @cStudentID char(9)
as
begin
	delete from Marks where cStudentID = @cStudentID
end
go
--procedure get Marks by StudentID
create procedure prcGetMarksByStudentID @cStudentID char(9)
as
begin
	select * from Marks where cStudentID = @cStudentID
end
go
--procedure get all Marks
create procedure prcGetAllMarks 
as
begin
	select * from Marks
end
go
--Procedure Get All Marks 2
create procedure prcGetAllMarks2
as
begin
	select a.cStudentID 'StudentID',e.vPersonName 'Name',a.iMark 'Marks'
	from Marks a, Student b, Class c, Course d,Person e 
	where a.cStudentID = b.cStudentID
	and b.cClassID = c.cClassID
	and c.cCourseID = d.cCourseID 
	and b.cStudentID = e.cPersonID
end
go
--Procedure Get Marks by CourseID
create procedure prcGetMarksByCourseID @cCourseID char(5)
as
begin
	select a.cStudentID 'StudentID',e.vPersonName 'Name',a.iMark 'Marks'
	from Marks a, Student b, Class c, Course d,Person e 
	where a.cStudentID = b.cStudentID
	and b.cClassID = c.cClassID
	and c.cCourseID = d.cCourseID 
	and b.cStudentID = e.cPersonID
	and d.cCourseID = @cCourseID
end
go
--Procedure Get marks by ClassID
create procedure prcGetMarksByClassID @cClassID char(6)
as
begin
	select a.cStudentID 'StudentID',d.vPersonName 'Name',a.iMark 'Marks'
	from Marks a, Student b, Class c,Person d
	where a.cStudentID = b.cStudentID
	and b.cClassID = c.cClassID
	and b.cStudentID = d.cPersonID
	and c.cClassID = @cClassID
end
go
--Procedure Get marks by CourseID and ClassID
create procedure prcGetMarksByCourseIDAndClassID @cCourseID char(5),@cClassID char(6)
as
begin
	select a.cStudentID 'StudentID',e.vPersonName 'Name',a.iMark 'Marks'
	from Marks a, Student b, Class c, Course d,Person e 
	where a.cStudentID = b.cStudentID
	and b.cClassID = c.cClassID
	and c.cCourseID = d.cCourseID 
	and b.cStudentID = e.cPersonID
	and d.cCourseID = @cCourseID
	and c.cClassID = @cClassID
end
go
--Procedure Get marks by CourseID and ClassID 1
create procedure prcGetMarksByCourseIDAndClassID1 @cCourseID char(5),@cClassID char(6)
as
begin
	select a.cStudentID,e.vPersonName,e.dPersonBirth,a.iMark
	from Marks a, Student b, Class c, Course d,Person e 
	where a.cStudentID = b.cStudentID
	and b.cClassID = c.cClassID
	and c.cCourseID = d.cCourseID 
	and b.cStudentID = e.cPersonID
	and d.cCourseID = @cCourseID
	and c.cClassID = @cClassID
end
go
----------------------------------------ROLLCALL--------------------------------------------
--Procedure insert Rollcall
create procedure prcInsertRollcall @cStudentID char(9),@dRollcallDay datetime,@iAttend int
as
begin
	insert Rollcall values(@cStudentID,@dRollcallDay,@iAttend)
end
go
--Procedure update Rollcall
create procedure prcUpdateRollcall @cStudentID char(9),@dRollcallDay datetime,@iAttend int
as
begin
	update Rollcall
	set iAttend = @iAttend
	where cStudentID = @cStudentID and dRollcallDay = @dRollcallDay
end
go
--Procedure delete rollcall
create procedure prcDeleteRollcall @cStudentID char(9),@dRollcallDay datetime
as
begin
	delete from Rollcall where cStudentID = @cStudentID and dRollcallDay = @dRollcallDay
end
go
--Procedure delete rollcall by StudentID
create procedure prcDeleteRollcallByStudentID @cStudentID char(9)
as
begin
	delete from Rollcall where cStudentID = @cStudentID 
end
go
--Procedure get Rollcall by StudentID1
create procedure prcGetRollcallsByStudentID1 @cStudentID char(9)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
as
begin
	select * from Rollcall where cStudentID = @cStudentID
end
go
--Procedure get Rollcall by StudentID1
create procedure prcGetRollcallByRollcallDay @dRollcallDay datetime                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
as
begin
	select * from Rollcall where dRollcallDay = @dRollcallDay
end
go
--Procedure get Rollcall by StudentID
create procedure prcGetRollcallsByStudentID @cStudentID char(9)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
as
begin
	select a.cStudentID 'StudentID',c.vPersonName 'Name',a.dRollcallDay 'RollcallDay',a.iAttend 'Attend'
	from Rollcall a,Student b,Person c
	where a.cStudentID = b.cStudentID
	and b.cStudentID = c.cPersonID
	and a.cStudentId = @cStudentID
end
go
--Procedure get Rollcall by RollcallDay
create procedure prcGetRollcallsByRollcallDay @dRollcallDay datetime                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
as
begin
	select a.cStudentID 'StudentID',c.vPersonName 'Name',a.dRollcallDay 'RollcallDay',a.iAttend 'Attend'
	from Rollcall a,Student b,Person c
	where a.cStudentID = b.cStudentID
	and b.cStudentID = c.cPersonID
	and a.dRollcallDay = @dRollcallDay
end
go
--Procedure get Rollcall by StudentID and RollcallDay
--create procedure prcGetRollcallsByStudentIDAndRollcallDay @cStudentID char(9),@dRollcallDay datetime                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
--as
--begin
--	select a.cStudentID 'StudentID',c.vPersonName 'Name',a.dRollcallDay 'RollcallDay',a.iAttend
--	from Rollcall a,Student b,Person c
--	where a.cStudentID = b.cStudentID
--	and b.cStudentID = c.cPersonID
--	and a.cStudentId = @cStudentID
--	and a.dRollcallDay = @dRollcallDay
--end
create procedure prcGetRollcallByStudentIDAndRollcallDay @cStudentID char(9),@dRollcallDay datetime
as
begin
	select * from Rollcall where cStudentID = @cStudentID and dRollcallDay = @dRollcallDay
end
go
--Procedure get all rollcall
create procedure prcGetAllRollcalls                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
as
begin
	select * from Rollcall 
end
go
--Procedure get Rollcall by CourseID
create procedure prcGetRollcallsByCourseID @cCourseID char(5)
as
begin
	select a.cStudentID 'StudentID',c.vPersonName 'Name',a.dRollcallDay 'RollcallDay',a.iAttend 'Attend'
	from Rollcall a,Student b,Person c,Class d,Course e
	where a.cStudentID = b.cStudentID
	and b.cStudentID = c.cPersonID
	and b.cClassID = d.cClassID
	and d.cCourseID = e.cCourseID
	and e.cCourseID = @cCourseID
end
go
--Procedure get Rollcall by ClassID
create procedure prcGetRollcallsByClassID @cClassID char(6)
as
begin
	select a.cStudentID 'StudentID',c.vPersonName 'Name',a.dRollcallDay 'RollcallDay',a.iAttend 'Attend'
	from Rollcall a,Student b,Person c,Class d
	where a.cStudentID = b.cStudentID
	and b.cStudentID = c.cPersonID
	and b.cClassID = d.cClassID
	and d.cClassID = @cClassID
end
go
----------------------------------------FEETYPE--------------------------------------------
--Procredure insert FeeType
create procedure prcInsertFeeType @vTypeName varchar(30),@iFee int
as
begin
	declare @maxId_num char(3)
	if(exists(select * from FeeType))
	begin	
		select top 1 @maxId_num=cTypeID 
		from FeeType
		order by right(cTypeID,2) desc
		set @maxId_num='T'+convert(char(2),convert(int,right(@maxId_num,2)+1))
	end
	else
	begin
		set @maxId_num='T11'
	end
	insert into FeeType
	values(@maxId_num,@vTypeName,@iFee)
end
go
--procedure update FeeType
create procedure prcUpdateFeeType @cTypeID char(3),@vTypeName char(30),@iFee int
as
begin
	update FeeType
	set vTypeName = @vTypeName,iFee = @iFee
	where cTypeID = @cTypeID
end
go
--procedure delete FeeType
create procedure prcDeleteFeeType @cTypeID char(3)
as
begin
	Delete
	from FeeType
	where cTypeID = @cTypeID
end
go
--Procedure get FeeType by TypeID
create procedure prcGetFeeTypeByID @cTypeID char(3)
as
begin
	select * 
	from FeeType
	where cTypeID = @cTypeID
end
go
--procedure get all FeeType
create procedure prcGetAllFeeTypes 
as
begin
	select * from FeeType
end
go

----------------------------------------FEE--------------------------------------------
--Procedure insert Fee
create procedure prcInsertFee @cStudentID char(9),@cTypeID char(3),@iFeePaid int
as
begin
	insert Fee values(@cStudentID,@cTypeID,@iFeePaid)
end
go
--Procredure Update Fee
create procedure prcUpdateFee @cStudentID char(9),@cTypeID char(3),@iFeePaid int
as
begin
	update Fee
	set cTypeID = @cTypeID,iFeePaid = @iFeePaid
	where cStudentID = @cStudentID
end
go
--Procredure Delete Fee
create procedure prcDeleteFee @cStudentID char(9)
as
begin
	Delete 
	from Fee
	where cStudentID = @cStudentID
end
go
--Procedure Get Fee by StudentID
create procedure prcGetFeeByStudentID @cStudentID char(9)
as
begin
	select * from Fee where cStudentID = @cStudentID
end
go
--Procedure Get Fee by Status
create procedure prcGetAllFees2
as
begin
	select a.cStudentID 'StudentID',c.vPersonName 'Name',b.iFee 'Fee',a.iFeePaid 'State' 
	from Fee a, FeeType b, Person c 
	where a.cTypeID = b.cTypeID 
	and a.cStudentID = c.cPersonID
end
go
--Procedure Get Fee by Status
create procedure prcGetFeeByStatus @iFeePaid int
as
begin
	select a.cStudentID 'StudentID',c.vPersonName 'Name',b.iFee 'Fee',a.iFeePaid 'State' 
	from Fee a, FeeType b, Person c 
	where a.cTypeID = b.cTypeID 
	and a.cStudentID = c.cPersonID
	and a.iFeePaid = @iFeePaid
end
go
--Procedure Get Fee by ClassID
create procedure prcGetFeeByClassID @cClassID char(6)
as
begin
	select a.cStudentID 'StudentID',e.vPersonName 'Name',d.iFee 'Fee',a.iFeePaid 'State' 
	from Fee a,Student b,Class c,FeeType d,Person e 
	where a.cStudentID = b.cStudentID
	and a.cStudentID = e.cPersonID 
	and b.cClassID= c.cClassID 
	and a.cTypeID = d.cTypeID 
	and c.cClassID = @cClassID
end
go
--Procedure Get Fee by Status and ClassID
create procedure prcGetFeeByStatusAndClassID @iFeePaid int,@cClassID char(6)
as
begin
	select a.cStudentID 'StudentID',e.vPersonName 'Name',d.iFee 'Fee',a.iFeePaid 'State' 
	from Fee a,Student b,Class c,FeeType d,Person e 
	where a.cStudentID = b.cStudentID
	and a.cStudentID = e.cPersonID
	and b.cClassID = c.cClassID 
	and a.cTypeID = d.cTypeID
	and c.cClassID = @cClassID 
	and a.iFeePaid = @iFeePaid
end
go
--Procedure get all fees 
create procedure prcGetAllFees 
as
begin
	select * from Fee
end


--create procedure prcGetNewPersonID2
--as
--begin
--	declare @cPersonID char(9) 
--	select top 1 @cPersonID = cPersonID
--	from Person
--	order by right(cPersonID,8)desc
--	return Convert(int,right(@cPersonID,8))
--end
--prcGetNewPersonID2
--
--select top 1 cPersonID
--	from Person
--	order by right(cPersonID,8)desc
--select * from login

--create procedure prcGetAccountByLoginIDAndPassword @cLoginID varchar(20),@vPassword char(15)
--as
--begin
--	select * from Login	where cLoginID = @cLoginID and vPassword = @vPassword
--end
--
--select * from Login where cLoginID = 'avan1' and vPassword = '12345678@X'
--
--select * from Login
--prcGetFeeByClassID 'Cl1001'
--prcGetAllStudentsOfClass 'Cl1001','P10000008'
--prcGetStaffByClassID 'Cl1001'
--prcGetTrainerByClassID 'CL1001'
--prcGetSubjectDetailsByClassID 'CL1001'
--insert Course values('C1001','faefwa')
--prcGetSubjectDetailsByClassIDAndStaffIDAndSubjectID 'S1002','P10000008','CL1001'
--prcGetRollcallsByClassID 'CL1001'
--prcGetClassByCourseID 'C1001'
--prcGetMarksByCourseIDAndClassID1 'C1001','CL1001'



